The Advanced mode gives you powerful methods to query, filter, display, and report your results. The steps to build a query in Advanced mode are the same as in Basic mode, with additional options described in the following sections.
Use Advanced mode to define queries with calculations and/or SQL expressions, and to aggregate data. You can set up complex filters and assign aliases to field names for display.
To view examples of queries built in Advanced mode, see Query examples using Advanced mode.
Advanced options for Summary
In Advanced mode, the Summary page provides a field to define any SQL statement for your query.
Advanced options for Sources
In Advanced mode, the following options are available on the Sources page:
■ Use FORCE ORDER option: Require SQL Server to join the tables in a query in the order you specify.
■ Use NOLOCK locking hint: Require SQL Server to ignore record locks.
■ Use join method for security enforcement: If enabled, when users run the query, iMIS checks to make sure they have permission to read every source within the query before displaying the results, whether the source is a business object, another query, or both. When disabled, iMIS does not check the security for every source and instead only enforces the security setting for the main query.
Advanced options for Filters
Filters refine a query. You can limit your results by eliminating duplicates, using comparisons, setting parameters, and using logical operators.
■ Tips for filters
□ If you installed iMIS on a case-sensitive SQL server, the filters you define are case-sensitive. You cannot define case-insensitive filters on queries when iMIS runs on a case-sensitive SQL Server.
□ Select the Require user to provide at least one value option when you want to require a value for every filter you define for a query.
□ Selecting the Remove icon for a filter set removes both the filter set and all related filters from the query.
Advanced mode options for Display
In Advanced mode, the following options are available on the Display page:
■ Function: Allows the user to find Count and Min or Max on a field.
■ Alias: Creates a label name for the field to be displayed to users. The alias entered can contain special characters.
Use key_ or code_ as a prefix in the Alias field to hide columns under certain conditions:
□ If you use code_ as a prefix, this field will always be hidden from the initial result set.
□ If you use key_ as a prefix, this field will be hidden if the current column is not a GUID.
■ Link: You can add a link for each column listed in the Display tab so that the user can click on the link to go to a specified page for more details. See Linking IQA results for more information.
■ Subtotal: Specify whether the property should be subtotaled in the display. You can display query results in a hierarchical grid of summary rows and detail rows by using the Subtotal option. See Hierarchical grid display for more information.